package com.legendframework.core.dao.sql;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.legendframework.core.dao.AbstractDao;
import com.legendframework.core.dao.Return;
import com.legendframework.core.dao.annotation.Column;
import com.legendframework.core.dao.annotation.Entity;
import com.legendframework.core.dao.annotation.TableId;
import com.legendframework.core.dao.exception.DaoException;
import com.legendframework.core.dao.page.Page;
import com.legendframework.core.dao.annotation.ToJson;
import com.legendframework.core.dao.sql.core.ITable;
import com.legendframework.core.dao.sql.core.JdbcTemplate;
import com.legendframework.core.dao.support.ClassUtils;
import com.legendframework.core.dao.transaction.JdbcTransaction;
import com.legendframework.core.dao.utils.DaoStringUtils;
import com.legendframework.core.dao.wrapper.Wrapper;
import javax.sql.DataSource;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

public class SqlDaoImpl<T> extends AbstractDao<T> implements ITable {

    /**
     * jdbc操作
     */
    private JdbcTemplate jdbcTemplate;

    private Gson gson;

    public SqlDaoImpl() {
        super(null);
    }

    public SqlDaoImpl(Class<T> entityType , DataSource dataSource , JdbcTransaction jdbcTransaction) {
        super(entityType);
        this.jdbcTemplate = new JdbcTemplate(dataSource,jdbcTransaction);
        this.gson = new GsonBuilder().serializeNulls().create();

        //表是否不存在
        boolean tableExists = isTableExists();
        //创建表
        createTable();
        if (!tableExists) {
            saveDefaultEntity();
        }
    }

    /**
     * 获取表名
     * @return
     */
    private String getTableName(){
        String tableName = DaoStringUtils.humpToLine(entityType.getSimpleName());
        if (entityType.isAnnotationPresent(Entity.class)) {
            Entity annotation = entityType.getAnnotation(Entity.class);
            if (DaoStringUtils.isNotEmpty(annotation.sqlTableName())) {
                tableName = DaoStringUtils.humpToLine(annotation.sqlTableName());
            }
        }
        return tableName;
    }

    /**
     * 获取表的ID字段名称
     * @return
     */
    private String getTableIdField(){
        List<Field> declaredFields = ClassUtils.getByClass(entityType);
        for (Field declaredField : declaredFields) {
            if (declaredField.isAnnotationPresent(TableId.class)) {
                TableId annotation = declaredField.getAnnotation(TableId.class);
                if (DaoStringUtils.isEmpty(annotation.value())) {
                    return DaoStringUtils.humpToLine(declaredField.getName());
                }
                return DaoStringUtils.humpToLine(annotation.value());
            }
        }
        throw new IllegalArgumentException("实体类: "+entityType.getName()+" 没有@TableId标识,找不到主键ID");
    }

    /**
     * 获取表所有字段
     * @return
     */
    private String[] getTableFields(){
        List<Field> declaredFields = ClassUtils.getByClass(entityType);
        List<String> tableFields = new ArrayList<>();
        for (Field declaredField : declaredFields) {
            //字段名转下划线
            String tableFieldName = DaoStringUtils.humpToLine(declaredField.getName());
            if (declaredField.isAnnotationPresent(Column.class)) {
                Column annotation = declaredField.getAnnotation(Column.class);
                if (!annotation.exist()) {
                    continue;
                }
                if (DaoStringUtils.isNotEmpty(annotation.value())) {
                    //转下划线
                    tableFieldName = DaoStringUtils.humpToLine(annotation.value());
                }
            }
            if (declaredField.isAnnotationPresent(TableId.class)) {
                TableId annotation = declaredField.getAnnotation(TableId.class);
                if (DaoStringUtils.isNotEmpty(annotation.value())) {
                    //转下划线
                    tableFieldName = DaoStringUtils.humpToLine(annotation.value());
                }
            }
            tableFields.add(tableFieldName);
        }
        return tableFields.toArray(new String[tableFields.size()]);
    }

    /**
     * 获取表所有字段的值
     * @return
     */
    private Object[] getFieldValues(T t){
        List<Field> declaredFields = ClassUtils.getByClass(entityType);
        List<Object> tableFieldValues = new ArrayList<>();
        for (Field declaredField : declaredFields) {
            if (declaredField.isAnnotationPresent(Column.class)) {
                Column annotation = declaredField.getAnnotation(Column.class);
                if (!annotation.exist()) {
                    continue;
                }
            }
            String getterMethodName = DaoStringUtils.fieldNameToGetterMethodName(declaredField.getName());
            Method method = null;
            try {
                method = t.getClass().getMethod(getterMethodName);
                Object invoke = method.invoke(t);
                if (invoke != null) {
                    if (declaredField.isAnnotationPresent(ToJson.class)) {
                        invoke = gson.toJson(invoke);
                    }
                    if (invoke instanceof Enum) {
                        invoke = invoke.toString();
                    }
                }
                tableFieldValues.add(invoke);
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return tableFieldValues.toArray(new Object[tableFieldValues.size()]);
    }

    /**
     * 获取查询语句的前缀
     * @return
     */
    private String getSelectSqlPrefix(){
        return "select * from `" + getTableName() + "` ";
    }

    /**
     * 查询数量
     * @return
     */
    private String getSelectCountSqlPrefix(){
        return "select count(1) from `" + getTableName()+ "` ";
    }

    /**
     * 获取分页数据
     * @param page
     * @return
     */
    private Return<String,Object[]> getLimitSql(Page<T> page){
        if (page == null) {
            return null;
        }
        return new Return<String, Object[]>() {
            @Override
            public String getR1() {
                return " limit ? ?";
            }

            @Override
            public Object[] getR2() {
                return new Object[]{(page.getCurrentPage() - 1) * page.getItemNum() , page.getItemNum()};
            }
        };
    }

    /**
     * 获取修改语句的前缀
     * @return
     */
    private Return<String,List<Object>> getUpdateSqlPrefix(T entity){
        String[] tableFields = getTableFields();
        Object[] fieldValues = getFieldValues(entity);
        if (tableFields.length != fieldValues.length){
            throw new RuntimeException("SQL: 类字段与值不匹配或者为null");
        }
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE `");
        sql.append(getTableName());
        sql.append("` set ");
        List<Object> objects = new ArrayList<>();
        int count = 0;
        for (int i = 0; i < tableFields.length; i++) {
            String tableField = tableFields[i];
            //如果是主键,则跳过
            if (DaoStringUtils.equals(tableField, getTableIdField())) {
                continue;
            }
            //如果该值为NULL则跳过
            if (fieldValues[i] == null) {
                continue;
            }
            if (count != 0) {
                sql.append(" , ");
            }
            sql.append("`");
            sql.append(tableField);
            sql.append("`");
            sql.append(" = ?");
            count++;
            objects.add(fieldValues[i]);
        }
        return new Return<String, List<Object>>() {
            @Override
            public String getR1() {
                return sql.toString();
            }

            @Override
            public List<Object> getR2() {
                return objects;
            }
        };
    }

    /**
     * 获取删除语句的前缀
     * @return
     */
    private String getDeleteSqlPrefix(){
        return "delete from `" + getTableName() + "` ";
    }

    /**
     * 获取whereById的where语句
     * @return
     */
    private String getWhereById(){
        return " where `"+getTableIdField()+"` = ? ";
    }

    private Return<String,List> getWhereByIdIn(Collection<? extends Serializable> idList){
        if (idList == null || idList.isEmpty()) {
            throw new DaoException("deleteBatchIds,入参为空");
        }
        StringBuilder str = new StringBuilder(getDeleteSqlPrefix());
        str.append(" where ");
        str.append(getTableIdField());
        str.append(" in (");
        for (int j = 0; j < idList.size(); j++) {
            if (j != 0) {
                str.append(" , ");
            }
            str.append(" ? ");
        }
        str.append(" ) ");
        return new Return<String, List>() {
            @Override
            public String getR1() {
                return str.toString();
            }

            @Override
            public List getR2() {
                return new ArrayList<>(idList);
            }
        };
    }

    @Override
    public int insert(T entity) {
        String[] tableFields = getTableFields();
        Object[] fieldValues = getFieldValues(entity);
        if (tableFields.length != fieldValues.length){
            throw new RuntimeException("SQL: 类字段与值不匹配或者为null");
        }
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO `");
        sql.append(getTableName());
        sql.append("`(");
        for (int i = 0; i < tableFields.length; i++) {
            String tableField = tableFields[i];
            if (i != 0) {
                sql.append(",");
            }
            sql.append("`");
            sql.append(tableField);
            sql.append("`");
        }
        sql.append(") VALUES (");
        for (int i = 0; i < fieldValues.length; i++) {
            if (i != 0) {
                sql.append(",");
            }
            sql.append("?");
        }
        sql.append(");");
        return jdbcTemplate.update(sql.toString(), fieldValues);
    }

    @Override
    public int deleteById(Serializable id) {
        return jdbcTemplate.update(DaoStringUtils.join(getDeleteSqlPrefix(),getWhereById()), id);
    }

    @Override
    public int delete(Wrapper<T> wrapper) {
        if (wrapper == null) {
            return jdbcTemplate.update(getDeleteSqlPrefix());
        }
        Return<String, Object[]> sqlRe = wrapper.toSql();
        return jdbcTemplate.update(DaoStringUtils.join(getDeleteSqlPrefix(),sqlRe.getR1()),sqlRe.getR2());
    }

    @Override
    public int deleteBatchIds(Collection<? extends Serializable> idList) {
        if (idList == null || idList.isEmpty()) {
            throw new DaoException("deleteBatchIds,入参为空");
        }
        Return<String, List> whereByIdIn = getWhereByIdIn(idList);
        return jdbcTemplate.update(DaoStringUtils.join(getDeleteSqlPrefix(),whereByIdIn.getR1(),whereByIdIn.getR2().toArray()));
    }

    @Override
    public int updateById(T entity) {
        Return<String, List<Object>> updateSqlPrefix = getUpdateSqlPrefix(entity);
        updateSqlPrefix.getR2().add(ClassUtils.getIdByEntity(entity));
        return jdbcTemplate.update(DaoStringUtils.join(updateSqlPrefix.getR1(),getWhereById()),updateSqlPrefix.getR2().toArray());
    }

    @Override
    public int update(T entity, Wrapper<T> updateWrapper) {
        Return<String, List<Object>> updateSqlPrefix = getUpdateSqlPrefix(entity);
        String sql = updateSqlPrefix.getR1();
        if (updateWrapper != null) {
            Return<String, Object[]> wrapper = updateWrapper.toSql();
            sql += wrapper.getR1();
            updateSqlPrefix.getR2().addAll(Arrays.asList(wrapper.getR2()));   
        }
        return jdbcTemplate.update(sql,updateSqlPrefix.getR2().toArray());
    }

    @Override
    public T selectById(Serializable id) {
        return jdbcTemplate.findOne(DaoStringUtils.join(getSelectSqlPrefix(),getWhereById()),entityType,id);
    }

    @Override
    public List<T> selectBatchIds(Collection<? extends Serializable> idList) {
        Return<String, List> whereByIdIn = getWhereByIdIn(idList);
        return jdbcTemplate.findAll(getSelectSqlPrefix(),entityType,whereByIdIn.getR2().toArray());
    }

    @Override
    public T selectOne(Wrapper<T> queryWrapper , boolean throwEx) {
        if (queryWrapper == null) {
            return jdbcTemplate.findOne(getSelectSqlPrefix(),entityType,throwEx,new Object[0]);
        }
        Return<String, Object[]> wrapper = queryWrapper.toSql();
        return jdbcTemplate.findOne(DaoStringUtils.join(getSelectSqlPrefix(),wrapper.getR1()),entityType,throwEx,wrapper.getR2());
    }

    @Override
    public long selectCount(Wrapper<T> queryWrapper) {
        if (queryWrapper == null) {
            return jdbcTemplate.findCount(getSelectCountSqlPrefix());
        }
        Return<String, Object[]> wrapper = queryWrapper.toSql();
        return jdbcTemplate.findCount(DaoStringUtils.join(getSelectCountSqlPrefix(),wrapper.getR1()),wrapper.getR2());
    }

    @Override
    public List<T> selectList(Wrapper<T> queryWrapper) {
        if (queryWrapper == null) {
            return jdbcTemplate.findAll(getSelectSqlPrefix(),entityType);
        }
        Return<String, Object[]> wrapper = queryWrapper.toSql();
        return jdbcTemplate.findAll(DaoStringUtils.join(getSelectSqlPrefix(),wrapper.getR1()),entityType,wrapper.getR2());
    }

    @Override
    public Page<T> selectPage(Page<T> page, Wrapper<T> queryWrapper) {
        String sqlSuffix = "";
        List params = new ArrayList();
        Return<String, Object[]> wrapper = null;
        if (queryWrapper != null) {
            wrapper = queryWrapper.toSql();
            sqlSuffix += wrapper.getR1();
            params.addAll(Arrays.asList(wrapper.getR2()));
        }
        Return<String, Object[]> limitSql = getLimitSql(page);
        if (limitSql != null) {
            sqlSuffix += limitSql.getR1();
            params.addAll(Arrays.asList(limitSql.getR2()));
        }
        List<T> all = jdbcTemplate.findAll(DaoStringUtils.join(getSelectSqlPrefix(), sqlSuffix), entityType, params.toArray());
        page.setTotalSum(jdbcTemplate.findCount(DaoStringUtils.join(getSelectCountSqlPrefix(),wrapper != null ? wrapper.getR1() : ""),wrapper != null ? wrapper.getR2() : new Object[0]));
        page.setTotalPage(page.getTotalSum() % page.getItemNum() == 0 ? page.getTotalSum() / page.getItemNum() : page.getTotalSum() / page.getItemNum() + 1);
        page.setList(all);
        return page;
    }

    @Override
    public boolean isTableExists() {
        String sql = "SELECT COUNT(1) FROM information_schema.TABLES WHERE TABLE_SCHEMA = (select database()) and table_name ='"+getTableName()+"';";
        long update = jdbcTemplate.findCount(sql);
        return update > 0;
    }

    @Override
    public boolean createTable() {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS `");
        sb.append(getTableName());
        sb.append("` (");
        for (Field declaredField : ClassUtils.getByClass(entityType)) {
            String tableFieldName = DaoStringUtils.humpToLine(declaredField.getName());
            if (declaredField.isAnnotationPresent(TableId.class)) {
                TableId annotation = declaredField.getAnnotation(TableId.class);
                if (DaoStringUtils.isNotEmpty(annotation.value())) {
                    tableFieldName = DaoStringUtils.humpToLine(annotation.value());
                }
                sb.append("`");
                sb.append(tableFieldName);
                sb.append("`");
                sb.append(" VARCHAR(50) NOT NULL,");
                sb.append(" PRIMARY KEY (`");
                sb.append(tableFieldName);
                sb.append("`)");
            } else {
                if (declaredField.isAnnotationPresent(Column.class)) {
                    Column annotation = declaredField.getAnnotation(Column.class);
                    if (!annotation.exist()) {
                        continue;
                    }
                    if (DaoStringUtils.isNotEmpty(annotation.value())) {
                        tableFieldName = DaoStringUtils.humpToLine(annotation.value());
                    }
                }
                sb.append("`");
                sb.append(tableFieldName);
                sb.append("`");
                if (declaredField.isAnnotationPresent(ToJson.class)) {
                    sb.append(" TEXT(0)");
                }else {
                    if (declaredField.getType() == Integer.class) {
                        sb.append(" INT(11)");
                    } else if (declaredField.getType() == Float.class || declaredField.getType() == Double.class) {
                        sb.append(" DECIMAL(50,2)");
                    } else if (declaredField.getType() == Boolean.class) {
                        sb.append(" TINYINT(1)");
                    } else if (declaredField.getType() == Date.class || declaredField.getType() == java.sql.Date.class) {
                        sb.append(" DATETIME");
                    } else {
                        sb.append(" VARCHAR(100)");
                    }
                }
                sb.append(" DEFAULT NULL ");
            }
            sb.append(",");
        }
        sb.deleteCharAt(sb.length()-1);
        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
        int update = jdbcTemplate.update(sb.toString());
        return update > 0;
    }

    @Override
    public T getOne() {
        List<T> ts = selectList(null);
        if (ts.size() == 1) {
            return ts.get(0);
        }
        if (ts.size() == 0) {
            return null;
        }
        throw new DaoException("数据大于1");
    }
}
